using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using DTO;
using System.Data.OleDb;

//hien
namespace DAO
{
    //public class HinhThucBanDao : IDaoObject<HinhThucBanDto, int>
    public class HinhThucBanDao
    {
        public static HinhThucBanDto SaveOrUpdate(HinhThucBanDto obj)
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection connection = MyConnection.ConnectionData();
            //Make transaction for saving bill
            OleDbTransaction transaction = connection.BeginTransaction();
            try
            {
                transaction.Begin();

                string sqlPattern;
                if (obj.Id > 0)
                {
                    //Update hinhthucban table
                    sqlPattern = "Update Hinhthucban set Tenbenhnhan='" + obj.TenBenhNhan + "', set Trieuchung='" + obj.TrieuChung +
                       "', set Bacsiketoa='" + obj.BacSiKeToa.Id + "' where ID='" + obj.Id + "'";
                    OleDbCommand cmd = new OleDbCommand(sqlPattern, connection);
                    //int hinhthucbanid = cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
                    //obj.Id = hinhthucbanid;
                    //Insert or update duocpham table
                    foreach (DictionaryEntry entry in obj.Duocpham)
                    {
                        DuocPhamDao dpdao = new DuocPhamDao();
                        DuocPhamDto dto = dpdao.SaveOrUpdate((DuocPhamDto)entry.Value);
                        //insert detail table
                        sqlPattern = "Update CtHinhthucban set Soluongban='" + dto.SoLuongBan + "', Thanhtien='" +
                            dto.GiaXuat * dto.SoLuongBan + "') where HinhthucbanID=" + obj.Id + " and DuocphamID=" + dto.Id;
                        cmd.CommandText = sqlPattern;
                        cmd.ExecuteNonQuery();
                    }
                }
                else
                {
                    float tongtien = 0;
                    #region Insert Bacsiketoa first
                    if (obj.BacSiKeToa != null)
                    {
                        BacSiKeToaDao bacSiKeToaDao = new BacSiKeToaDao();
                        obj.BacSiKeToa = bacSiKeToaDao.SaveOrUpdate(obj.BacSiKeToa);
                    }
                    #endregion
                    #region Insert INTO Hinhthucban table
                    sqlPattern = "insert into Hinhthucban (HoadonID,Tenbenhnhan, Trieuchung, Bacsiketoa, Cachban)" +
                            " values ('{0}','{1}','{2}','{3}, {4}'";
                    string sqlString = String.Format(sqlPattern, obj.HoaDon.Id, obj.TenBenhNhan, obj.TrieuChung, obj.BacSiKeToa.Id, obj.CachBan);
                    OleDbCommand cmd = new OleDbCommand(sqlString, connection);
                    cmd.ExecuteNonQuery();
                    sqlString = "Select @@IDENTITY";
                    cmd = new OleDbCommand(sqlString, connection);
                    obj.Id = (int)cmd.ExecuteScalar();
                    #endregion
                    #region Handle for Duocpham Hashtable
                    foreach (DictionaryEntry entry in obj.Duocpham)
                    {
                        #region Update table duocpham minimum value and storeingValue
                        DuocPhamDao dpdao = new DuocPhamDao();
                        DuocPhamDto dto = dpdao.SaveOrUpdate((DuocPhamDto)entry.Value);
                        #endregion
                        #region Insert detail into CtHinhthucban table
                        dto.ThanhTien = dto.GiaXuat * dto.SoLuongBan;
                        sqlPattern = "insert into CtHinhthucban(HinhthucbanID, DuocphamID, Soluongban, Thanhtien)" +
                                " values ('{0}','{1}','{2}','{3}')";
                        sqlString = String.Format(sqlPattern, obj.Id, dto.Id, dto.SoLuongBan, dto.ThanhTien);
                        cmd.CommandText = sqlString;
                        cmd.ExecuteNonQuery();
                        #endregion
                        tongtien += dto.ThanhTien;
                    }
                    #endregion
                    #region update hinhthucban table for the total value
                    obj.HoaDon.TongTien = tongtien;
                    obj.HoaDon.SoTienThanhToan = tongtien + tongtien * obj.HoaDon.Vat / 100;
                    new HoaDonBanDao().SaveOrUpdate(obj.HoaDon);
                    #endregion
                    #region insert or update Tientrongngay table
                    TienTrongNgayDto tienTrongNgayDto = new TienTrongNgayDto();
                    tienTrongNgayDto.NgayBan = obj.HoaDon.NgayTao;
                    tienTrongNgayDto.TongTien += obj.HoaDon.TongTien;
                    TienTrongNgayDao tienTrongNgayDao = new TienTrongNgayDao();
                    tienTrongNgayDao.SaveOrUpdate(tienTrongNgayDto);
                    #endregion
                }

                transaction.Commit();
                return obj;
            }
            catch (Exception)
            {
                transaction.Rollback();
            }
            return null;
        }

        public HinhThucBanDto SaveOrUpdate(HinhThucBanDto obj, OleDbTransaction transaction)
        {
            throw new NotImplementedException();
        }

        public void Delete(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "Delete From CtHinhthucban where CtHinhthucban.HinhthucbanID='" + id + "' Delete From Hinhthucban Where ID='" + id + "'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            //cmd.Parameters.Add("@Ma", OleDbType.Integer);
            //cmd.Parameters["@Ma"].Value = manv;
            cmd.ExecuteNonQuery();
            cn.Close();

        }

        public HinhThucBanDto Select(int id)
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "select htb.*, bskt.* from Hinhthucban htb, Bacsiketoa bskt where bskt.ID=htb.BacsiketoaID and htb.ID='" + id + "'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            HinhThucBanDto htb = new HinhThucBanDto();
            while (dr.Read())
            {

                htb.Id = Convert.ToInt32(dr["ID"].ToString());
                htb.TenBenhNhan = dr["Tenbenhnhan"].ToString();
                htb.TrieuChung = dr["Trieuchung"].ToString();
                //htb.HoaDon.Id = Convert.ToInt32(dr["HoadonID"].ToString());
                htb.CachBan = Convert.ToInt32(dr["Cachban"].ToString());
                BacSiKeToaDto bsktdto = new BacSiKeToaDto();
                bsktdto.Id = Convert.ToInt32(dr["BacsiketoaID"].ToString());
                bsktdto.TenBacSiKeToa = (string)dr["Tenbacsiketoa"];
                htb.BacSiKeToa = bsktdto;

                htb.Duocpham = GetDuocpham(htb.Id);
            }
            dr.Close();
            cn.Close();
            return htb;

        }

        public HinhThucBanDto[] SelectAll()
        {
            //throw new Exception("The method or operation is not implemented.");
            OleDbConnection cn = MyConnection.ConnectionData();
            const string strSql = "select htb.*, bskt.* from Hinhthucban htb, Bacsiketoa bskt where bskt.ID=htb.BacsiketoaID";
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List<HinhThucBanDto> listHtbdto = new List<HinhThucBanDto>();

            foreach (DataRow dr in dataTable.Rows)
            {
                HinhThucBanDto htbdto = new HinhThucBanDto();
                htbdto.Id = Convert.ToInt32(dr["ID"].ToString());
                htbdto.TenBenhNhan = dr["Tenbenhnhan"].ToString();
                htbdto.TrieuChung = dr["Trieuchung"].ToString();
                htbdto.CachBan = Convert.ToInt32(dr["Cachban"].ToString());
                //htbdto.HoaDon.Id = Convert.ToInt32(dr["HoadonID"].ToString());

                BacSiKeToaDto bsktdto = new BacSiKeToaDto();
                bsktdto.Id = Convert.ToInt32(dr["BacsiketoaID"].ToString());
                bsktdto.TenBacSiKeToa = (string)dr["Tenbacsiketoa"];
                htbdto.BacSiKeToa = bsktdto;

                htbdto.Duocpham = GetDuocpham(htbdto.Id);
                listHtbdto.Add(htbdto);
            }
            cn.Close();
            return listHtbdto.ToArray();
        }

        private static Hashtable GetDuocpham(int id)
        {
            #region select list duoc pham from CThoadon
            const string strPattern = "select DuocphamID from CtHinhthucban where HinhthucbanID={0}";
            string strSql = string.Format(strPattern, id);
            OleDbConnection cn = MyConnection.ConnectionData();
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            Hashtable hashDuocpham = new Hashtable();
            foreach (DataRow row in dataTable.Rows)
            {
                DuocPhamDao dao = new DuocPhamDao();
                DuocPhamDto dto = dao.Select(Convert.ToInt32(row["DuocphamID"].ToString()));
                hashDuocpham.Add(dto.MaDuocPham, dto);
            }
            return hashDuocpham;
            #endregion
        }

        public HinhThucBanDto[] SelectHinhThucBan(DateTime tngay, DateTime dngay, int hinhthucban)
        {
            //throw new Exception("The method or operation is not implemented.");
            try
            {
                OleDbConnection cn = MyConnection.ConnectionData();
                string strSql = "SELECT htb.ID as hinhthucbanID, htb.Tenbenhnhan, htb.Trieuchung, hd.Ngaytao";
                string bsketoa = "";
                if(hinhthucban == 2)  //hinh thuc ban la toa
                {
                    strSql += ", BSketoa.Tenbacsiketoa";
                    bsketoa = " INNER JOIN BSketoa ON htb.BacsiketoaID = BSketoa.ID";
                }
                strSql += " FROM  Hoadon AS hd,Hinhthucban AS htb " + bsketoa +
                          " WHERE htb.HoadonID=hd.ID and hd.Ngaytao>= DateSerial("
                          + tngay.Year + "," + tngay.Month + "," + tngay.Day +
                          ") and hd.Ngaytao<= DateSerial(" + dngay.Year + "," + dngay.Month + "," + dngay.Day +
                          ") and htb.Cachban=" + hinhthucban;
                OleDbCommand cmd = new OleDbCommand(strSql, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                List<HinhThucBanDto> list = new List<HinhThucBanDto>();

                while (dr.Read())
                {
                    HinhThucBanDto htb = new HinhThucBanDto();
                    HoaDonBanDto hdb = new HoaDonBanDto();
                    htb.TenBenhNhan = dr["Tenbenhnhan"].ToString();
                    if(hinhthucban==2)
                    {
                        htb.BacSiKeToa = new BacSiKeToaDto();
                        htb.BacSiKeToa.TenBacSiKeToa = dr["Tenbacsiketoa"].ToString();
                    }
                    htb.TrieuChung = dr["Trieuchung"].ToString();
                    hdb.NgayTao = DateTime.Parse(dr["Ngaytao"].ToString());
                    //add chi tiet thuoc
                    htb.Duocpham = GetListDuocphamForHinhthucban(dr["hinhthucbanID"].ToString());
                    //end add chi tiet thuoc
                    htb.HoaDon = hdb;
                    list.Add(htb);
                }
                dr.Close();
                cn.Close();
                return list.ToArray();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
        }

        public Hashtable GetListDuocphamForHinhthucban(string hinhthucbanid)
        {
            string strSql =
                "SELECT CtHinhthucban.HinhthucbanID, CtHinhthucban.Soluong, Duocpham.ID as DuocphamId, Duocpham.Tenduocpham,Duocpham.Gianhap,Duocpham.Giaxuat, Donviduocpham.Tendonvi, CtHinhthucban.Thanhtien" +
                " FROM (CtHinhthucban INNER JOIN Duocpham ON CtHinhthucban.DuocphamID = Duocpham.ID) INNER JOIN Donviduocpham ON Duocpham.Madonvi = Donviduocpham.ID " +
                " WHERE (((CtHinhthucban.HinhthucbanID)="+hinhthucbanid+"));";
            OleDbConnection connection = MyConnection.ConnectionData();
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql,connection);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            Hashtable hashDuocpham = new Hashtable();
            foreach (DataRow row in dataTable.Rows)
            {
                DuocPhamDto duocPhamDto = new DuocPhamDto();
                duocPhamDto.Id = Convert.ToInt32(row["DuocphamId"].ToString());
                duocPhamDto.TenDuocPham = row["Tenduocpham"].ToString();
                duocPhamDto.DonViDuocPham = new DonViDuocPhamDto();
                duocPhamDto.DonViDuocPham.TenDonVi = row["Tendonvi"].ToString();
                duocPhamDto.SoLuongBan = Convert.ToInt32(row["Soluong"].ToString());
                duocPhamDto.ThanhTien = Convert.ToInt32(row["Thanhtien"].ToString());
                duocPhamDto.GiaXuat = float.Parse(row["Giaxuat"].ToString());
                duocPhamDto.GiaNhap = float.Parse(row["Gianhap"].ToString());
                hashDuocpham.Add(duocPhamDto.Id, duocPhamDto);
            }
            return hashDuocpham;
        }

        public static HinhThucBanDto  ThemHinhThucBan(HinhThucBanDto obj)
        {
            //mo ket noi co so du lieu
            OleDbConnection cn = MyConnection.ConnectionData();
            OleDbTransaction transaction = cn.BeginTransaction();
            //
            try
            {
                if(obj.CachBan == 2 )
                {
                    BacSiKeToaDao bsketoaDao = new BacSiKeToaDao();
                    obj.BacSiKeToa = bsketoaDao.SaveOrUpdate(obj.BacSiKeToa, transaction);
                }
                //them hinh thuc ban vao co so du lieu
                string strSQL = "Insert into Hinhthucban(HoadonID, Tenbenhnhan, Trieuchung,BacsiketoaID,Cachban) values(?,?,?,?,?)";
                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                cmd.Transaction = transaction;
                cmd.Parameters.Add("@HoadonID", OleDbType.Integer);
                cmd.Parameters["@HoadonID"].Value = obj.HoaDon.Id;
                cmd.Parameters.Add("@Tenbenhnhan", OleDbType.WChar);
                cmd.Parameters["@Tenbenhnhan"].Value = obj.TenBenhNhan;
                cmd.Parameters.Add("@Trieuchung", OleDbType.WChar);
                cmd.Parameters["@Trieuchung"].Value = obj.TrieuChung;
                cmd.Parameters.Add("@BacsiketoaID", OleDbType.Integer);
                cmd.Parameters["@BacsiketoaID"].Value = obj.BacSiKeToa.Id;
                cmd.Parameters.Add("@Cachban", OleDbType.Integer);
                cmd.Parameters["@Cachban"].Value = obj.CachBan;
                cmd.ExecuteNonQuery();
                strSQL = "Select @@IDENTITY";
                cmd = new OleDbCommand(strSQL, cn);
                cmd.Transaction = transaction;
                obj.Id = (int)cmd.ExecuteScalar();
                //

                foreach (DictionaryEntry entry in obj.Duocpham)
                {
                    //them chi tiet hinh thuc ban vao co so du lieu
                    strSQL = "Insert into CtHinhthucban(HinhthucbanID, DuocphamID, Soluong,Thanhtien) values(?,?,?,?)";
                    cmd = new OleDbCommand(strSQL, cn);
                    cmd.Transaction = transaction;
                    cmd.Parameters.Add("@HinhthucbanID", OleDbType.Integer);
                    cmd.Parameters["@HinhthucbanID"].Value = obj.Id;
                    DuocPhamDto dp = (DuocPhamDto)entry.Value;
                    cmd.Parameters.Add("@DuocphamID", OleDbType.Integer);
                    cmd.Parameters["@DuocphamID"].Value = dp.Id;
                    cmd.Parameters.Add("@Soluong", OleDbType.Integer);
                    cmd.Parameters["@Soluong"].Value = dp.SoLuongBan;
                    cmd.Parameters.Add("@Thanhtien", OleDbType.Integer);
                    cmd.Parameters["@Thanhtien"].Value = dp.ThanhTien;
                    cmd.ExecuteNonQuery();
                    //

                    //cap nhat lai so luong duoc pham
                    strSQL = "Update Duocpham Set Soluongtonkho=? Where ID=?";
                    cmd = new OleDbCommand(strSQL, cn);
                    cmd.Transaction = transaction;
                    cmd.Parameters.Add("@Soluongtonkho", OleDbType.Integer);
                    cmd.Parameters["@Soluongtonkho"].Value = dp.SoLuongTonKho;
                    cmd.Parameters.Add("@ID", OleDbType.Integer);
                    cmd.Parameters["@ID"].Value = dp.Id;
                    cmd.ExecuteNonQuery();
                    
                }
                //
                transaction.Commit();
                cn.Close();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
            
            
            
            return obj;     
        }

        public DataTable SearchBenhNhanTheoTen(string name, DateTime ngaykham)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string query = "SELECT ht.ID,ht.Tenbenhnhan,ht.Trieuchung,dp.Tenduocpham,ct.Soluong,ct.Thanhtien, dvdp.Id as dpId, dvdp.Tendonvi ";
            query += " FROM CtHinhthucban ct, Hinhthucban ht,Duocpham dp,Hoadon hd, Donviduocpham dvdp ";
            query += " where ht.ID=ct.HinhthucbanID and hd.ID=ht.HoadonID and ct.DuocphamID=dp.ID ";
            query += " and ht.Tenbenhnhan like '" + name + "'" + " and format(hd.Ngaytao,'Short Date') like '" + ngaykham.ToShortDateString() + "' and dvdp.Id = dp.Madonvi";
            OleDbDataAdapter da = new OleDbDataAdapter(query, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}

